package requestCase

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object Popular_products_in_each_region_TOP3 {

  def main(args: Array[String]): Unit = {

    val conf = new SparkConf().setAppName("Popular products in each region").setMaster("local[*]")
    val sparkSession = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()

    sparkSession.sql(
      """
        |select
        |*
        |from (
        |	select
        |	*,
        |	rank() over(partition by area order by clickCnt desc)as rank
        |	from (
        |		select
        |		area,
        |		product_name,
        |		count(1) as clickCnt
        |		from (
        |			select
        |			*
        |			from user_visit_action a
        |			join product_info b on a.click_product_id=b.product_id
        |			join city_info c on a.city_id=c.city_id
        |			where click_product_id > -1
        |		)group by area, product_name
        |	)
        |)where rank<=3
        |""".stripMargin).show(false)

    sparkSession.stop()
  }

}
